{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Experimenting with storing the majority of object state information in a relational database (to make it easy to search) whilst wrapping this data in the appropriate object types (to enable easy processing).  The relational database will just be a set of pandas.DataFrame objects, not a full-blown DBMS.\n",
      "\n",
      "## CONCLUSIONS SO FAR\n",
      "\n",
      "* I think it's more trouble that it's worth to try to keep tables of data\n",
      "* Instead let's stick to the 'old' way of doing it:\n",
      "  * Dataset\n",
      "  * Dataset.appliances = ApplianceGroup of every appliance in the dataset\n",
      "  * Building.appliances = Dataset.appliances.select(building=building.id)\n",
      "  * ApplianceGroup.select is responsible for all the selection machinery.\n",
      "* So, when we first load a dataset, construct all Appliance objects, put them into Dataset.appliances."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd\n",
      "\n",
      "class Metadata(object):\n",
      "    \"\"\"Stores tables for appliance, dataset, building, electricity_meter, meter_tag etc\"\"\"\n",
      "    def __init__(self):\n",
      "        \"\"\"create dummy data\"\"\"\n",
      "        self.building = pd.DataFrame([['redd', 1, 'Washington'],\n",
      "                                      ['redd', 2, 'New York']],\n",
      "                                     columns=['dataset_id', 'building_instance', 'geo_location']).set_index(['dataset_id', 'building_instance'])\n",
      "        \n",
      "        # Construct objects and put these objects into the DataFrame.  \n",
      "        # We do this for several reasons:\n",
      "        #  - we can easily retrieve each object based on any\n",
      "        #    metadata column.\n",
      "        #  - we only construct a single object for each, um, object!\n",
      "        self.electricity_meter = pd.DataFrame(\n",
      "                                    [['redd', 1, 1, 'EnviR', -1, None],\n",
      "                                     ['redd', 1, 2, 'EnviR', 1, None],\n",
      "                                     ['redd', 1, 3, 'EnviR', 1, None]],\n",
      "                                    columns=['dataset_id', 'building_instance', 'meter_instance', \n",
      "                                             'meter_type_id', 'submeter_of', 'meter_obj'])#.set_index(['dataset_id', 'building_instance', 'meter_instance'])\n",
      "\n",
      "        for idx, meter in self.electricity_meter.iterrows():\n",
      "            print meter['submeter_of']\n",
      "            self.electricity_meter.loc[idx, 'submeter_of'] = 100\n",
      "            \n",
      "        for idx, meter in self.electricity_meter.iterrows():\n",
      "            print meter['submeter_of']\n",
      "        \n",
      "        \n",
      "        self.appliance_meter = pd.DataFrame([['redd', 1, 1, 'fridge', 1, None],\n",
      "                                             ['redd', 1, 1, 'washer dryer', 2, None],\n",
      "                                             ['redd', 1, 1, 'washer dryer', 3, None]], # dual supply\n",
      "                                             columns=['dataset_id', 'building_instance', 'appliance_instance', 'appliance_type', \n",
      "                                               'meter_instance', 'appliance_obj']).set_index(['dataset_id', 'building_instance', 'appliance_instance', 'appliance_type'])\n",
      "\n",
      "    \n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 163
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "md = Metadata()\n",
      "md.electricity_meter"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "-1\n",
        "1\n",
        "1\n",
        "100\n",
        "100\n",
        "100\n"
       ]
      },
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>dataset_id</th>\n",
        "      <th>building_instance</th>\n",
        "      <th>meter_instance</th>\n",
        "      <th>meter_type_id</th>\n",
        "      <th>submeter_of</th>\n",
        "      <th>meter_obj</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> EnviR</td>\n",
        "      <td> 100</td>\n",
        "      <td> None</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 2</td>\n",
        "      <td> EnviR</td>\n",
        "      <td> 100</td>\n",
        "      <td> None</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 3</td>\n",
        "      <td> EnviR</td>\n",
        "      <td> 100</td>\n",
        "      <td> None</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>3 rows \u00d7 6 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 164,
       "text": [
        "  dataset_id  building_instance  meter_instance meter_type_id  submeter_of  \\\n",
        "0       redd                  1               1         EnviR          100   \n",
        "1       redd                  1               2         EnviR          100   \n",
        "2       redd                  1               3         EnviR          100   \n",
        "\n",
        "  meter_obj  \n",
        "0      None  \n",
        "1      None  \n",
        "2      None  \n",
        "\n",
        "[3 rows x 6 columns]"
       ]
      }
     ],
     "prompt_number": 164
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "md.building"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th></th>\n",
        "      <th>geo_location</th>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>dataset_id</th>\n",
        "      <th>building_instance</th>\n",
        "      <th></th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th rowspan=\"2\" valign=\"top\">redd</th>\n",
        "      <th>1</th>\n",
        "      <td> Washington</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td>   New York</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>2 rows \u00d7 1 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 114,
       "text": [
        "                             geo_location\n",
        "dataset_id building_instance             \n",
        "redd       1                   Washington\n",
        "           2                     New York\n",
        "\n",
        "[2 rows x 1 columns]"
       ]
      }
     ],
     "prompt_number": 114
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "appliances = md.electricity_meter.merge(md.appliance)\n",
      "appliances"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>dataset_id</th>\n",
        "      <th>building_instance</th>\n",
        "      <th>meter_instance</th>\n",
        "      <th>meter_type_id</th>\n",
        "      <th>submeter_of</th>\n",
        "      <th>appliance_instance</th>\n",
        "      <th>appliance_type</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> EnviR</td>\n",
        "      <td>-1</td>\n",
        "      <td> 1</td>\n",
        "      <td>       fridge</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 2</td>\n",
        "      <td> EnviR</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> washer dryer</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 3</td>\n",
        "      <td> EnviR</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> washer dryer</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>3 rows \u00d7 7 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 35,
       "text": [
        "  dataset_id  building_instance  meter_instance meter_type_id  submeter_of  \\\n",
        "0       redd                  1               1         EnviR           -1   \n",
        "1       redd                  1               2         EnviR            1   \n",
        "2       redd                  1               3         EnviR            1   \n",
        "\n",
        "   appliance_instance appliance_type  \n",
        "0                   1         fridge  \n",
        "1                   1   washer dryer  \n",
        "2                   1   washer dryer  \n",
        "\n",
        "[3 rows x 7 columns]"
       ]
      }
     ],
     "prompt_number": 35
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>dataset_id</th>\n",
        "      <th>building_instance</th>\n",
        "      <th>meter_instance</th>\n",
        "      <th>meter_type_id</th>\n",
        "      <th>submeter_of</th>\n",
        "      <th>appliance_instance</th>\n",
        "      <th>appliance_type</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 2</td>\n",
        "      <td> EnviR</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> washer dryer</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 3</td>\n",
        "      <td> EnviR</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> washer dryer</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>2 rows \u00d7 7 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 52,
       "text": [
        "  dataset_id  building_instance  meter_instance meter_type_id  submeter_of  \\\n",
        "1       redd                  1               2         EnviR            1   \n",
        "2       redd                  1               3         EnviR            1   \n",
        "\n",
        "   appliance_instance appliance_type  \n",
        "1                   1   washer dryer  \n",
        "2                   1   washer dryer  \n",
        "\n",
        "[2 rows x 7 columns]"
       ]
      }
     ],
     "prompt_number": 52
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "class Appliance(object):\n",
      "\n",
      "    def __init__(self, metadata, dataset_id, building_instance, appliance_type, appliance_instance=1):\n",
      "        \"\"\"Copy relevant attributes as read-only attributes.\n",
      "        \"\"\"\n",
      "                \n",
      "        # Load appliance metadata\n",
      "        appliances = metadata.electricity_meter.merge(metadata.appliance)\n",
      "        appliance_table = appliances[(appliances['dataset_id']==dataset_id) & \n",
      "                                     (appliances['building_instance']==building_instance) &\n",
      "                                     (appliances['appliance_type']==appliance_type) &\n",
      "                                     (appliances['appliance_instance']==appliance_instance)]\n",
      "        \n",
      "        self._type = appliance_table['appliance_type'].values[0]\n",
      "        self._instance = appliance_table['appliance_instance'].values[0]\n",
      "        self._meters = []\n",
      "        for meter_instance in appliance_table['meter_instance'].values:\n",
      "            self._meters.append(Meter(metadata, dataset_id, building_instance, meter_instance))\n",
      "            \n",
      "        self._mains = Mains(metadata, dataset_id, building_instance)\n",
      "    \n",
      "\n",
      "    @property\n",
      "    def type(self):\n",
      "        return self._type\n",
      "    \n",
      "    @property\n",
      "    def instance(self):\n",
      "        return self._instance\n",
      "    \n",
      "    @property\n",
      "    def meters(self):\n",
      "        return self._meters\n",
      "    \n",
      "    def is_dual_supply(self):\n",
      "        return len(self.meters) == 2\n",
      "    \n",
      "    def __repr__(self):\n",
      "        return '%s(type=%s, instance=%s, is_dual_supply=%s)' % (self.__class__.__name__, \n",
      "                                                                self.type, \n",
      "                                                                self.instance,\n",
      "                                                                self.is_dual_supply())\n",
      "    \n",
      "    # TODO: how to get mains (for calculating proportion of energy)?  Or meter objects?"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 99
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "appliance = Appliance.from_metadata(md, 'redd', 1, 'washer dryer')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 100
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "appliance"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 101,
       "text": [
        "Appliance(type=washer dryer, instance=1, is_dual_supply=True)"
       ]
      }
     ],
     "prompt_number": 101
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "md.appliance"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>dataset_id</th>\n",
        "      <th>building_instance</th>\n",
        "      <th>appliance_instance</th>\n",
        "      <th>appliance_type</th>\n",
        "      <th>meter_instance</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td>       fridge</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> washer dryer</td>\n",
        "      <td> 2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> redd</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> washer dryer</td>\n",
        "      <td> 3</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "<p>3 rows \u00d7 5 columns</p>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 94,
       "text": [
        "  dataset_id  building_instance  appliance_instance appliance_type  \\\n",
        "0       redd                  1                   1         fridge   \n",
        "1       redd                  1                   1   washer dryer   \n",
        "2       redd                  1                   1   washer dryer   \n",
        "\n",
        "   meter_instance  \n",
        "0               1  \n",
        "1               2  \n",
        "2               3  \n",
        "\n",
        "[3 rows x 5 columns]"
       ]
      }
     ],
     "prompt_number": 94
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "fridge = Appliance.from_metadata(md, 'redd', 1, 'fridge')\n",
      "fridge._table['submeter_of'] = 100"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "-c:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.\n",
        "Try using .loc[row_index,col_indexer] = value instead\n"
       ]
      }
     ],
     "prompt_number": 98
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}